package com.jamy.song.dao;

import com.alibaba.fastjson.JSONObject;
import com.jamy.song.task.JamyTask;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Executor;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class UserDao {

    private ExecutorService executorService = Executors.newFixedThreadPool(5);
    private Connection conn;
    private JSONObject json;
    PreparedStatement ps = null;

    PreparedStatement ps0 = null;
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;
    PreparedStatement ps3 = null;
    PreparedStatement ps4 = null;


    public UserDao(Connection conn, JSONObject json) {
        this.conn = conn;
        this.json = json;
    }

    public void insertData() throws Exception {

        List<JSONObject> list = readFile("G:\\user.txt");

        String sql = "insert into tb_user2(name,age,address,card_no,open_date,toatl_amt,used_amt,extre_amt,create_time,create_user,update_time,update_user) " +
                "values(?,?,?,?,?,?,?,?,current_timestamp,'system',current_timestamp ,'system')";
        conn.setAutoCommit(false);
        ps = conn.prepareStatement(sql);
        try{
            for (int i=0; i<list.size(); i++) {
                JSONObject json = list.get(i);
                ps.setString(1, json.getString("name")+i);
                ps.setInt(2, json.getInteger("age"));
                ps.setString(3, json.getString("address"));
                ps.setString(4, json.getString("cardNo"));
                ps.setString(5, json.getString("openDate"));
                ps.setString(6, json.getString("totalAmt"));
                ps.setString(7, json.getString("usedAmt"));
                ps.setString(8, json.getString("extreAmt"));
                ps.addBatch();
                if (i > 0 && i%10000==0) {
                    ps.executeBatch();
                    conn.commit();
                }
            }
            ps.executeBatch();
        }catch(SQLException e){
            System.out.println("执行插入数据异常！");
        }finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null){
                conn.close();
            }
        }
    }

    private List<JSONObject> readFile(String path) throws Exception {
        List<JSONObject> list = new ArrayList<>();
        File file = new File(path);
        BufferedReader br = new BufferedReader(new FileReader(file));
        String st;
        while ((st = br.readLine()) != null ){
            JSONObject json = new JSONObject();
            String[] strs = st.split(",");
            json.put("name", strs[0]);
            json.put("age", strs[1]);
            json.put("address", strs[2]);
            json.put("cardNo", strs[3]);
            json.put("openDate", strs[4]);
            json.put("totalAmt", strs[5]);
            json.put("usedAmt", strs[6]);
            json.put("extreAmt", strs[7]);
            list.add(json);
        }
        return list;
    }

    public void readFileToDatabase(String path) throws Exception {

        String sql = "insert into dbo.tb_user4(name,age,address,card_no,open_date,toatl_amt,used_amt,extre_amt,create_time,create_user,update_time,update_user) " +
                "values(?,?,?,?,?,?,?,?,current_timestamp,'system',current_timestamp ,'system')";
        ps = conn.prepareStatement(sql);
        try{
            File file = new File(path);
            BufferedReader br = new BufferedReader(new FileReader(file));
            String st;
            int i = 0;
            while ((st = br.readLine()) != null ){
                i=i+1;
                String[] strs = st.split(",");
                ps.setString(1, strs[0]+i);
                ps.setInt(2, Integer.parseInt(strs[1]));
                ps.setString(3, strs[2]);
                ps.setString(4, strs[3]);
                ps.setString(5, strs[4]);
                ps.setString(6, strs[5]);
                ps.setString(7, strs[6]);
                ps.setString(8, strs[7]);
                ps.addBatch();
                if (i > 0 && i%10000==0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
        }catch(Exception e){
            System.out.println("执行插入数据异常:" + e);
        }finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null){
                conn.close();
            }
        }
    }

    public void readFileUpdateDatabase(String path) throws Exception {

        String sql = "update tb_user4 set name=?,age=?,address=?,card_no=?,open_date=?,toatl_amt=?,used_amt=?,extre_amt=?,create_time=current_timestamp,create_user='system',update_time=current_timestamp,update_user='jamysong' where id=?";
        ps = conn.prepareStatement(sql);
        try{
            File file = new File(path);
            BufferedReader br = new BufferedReader(new FileReader(file));
            String st;
            int i = 0;
            conn.setAutoCommit(false);
            while ((st = br.readLine()) != null ){
                i=i+1;
                String[] strs = st.split(",");
                ps.setString(1, strs[0]+"jamy"+i);
                ps.setInt(2, Integer.parseInt(strs[1]));
                ps.setString(3, strs[2]);
                ps.setString(4, strs[3]);
                ps.setString(5, strs[4]);
                ps.setString(6, strs[5]);
                ps.setString(7, strs[6]);
                ps.setString(8, strs[7]);
                ps.setString(9, Integer.toString(i));
                ps.addBatch();
                if (i > 0 && i%10000==0) {
                    ps.executeBatch();
                    ps.clearBatch();

                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
        }catch(Exception e){
            System.out.println("执行插入数据异常:" + e);
        }finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null){
                conn.close();
            }
        }
    }

    //分组事务提交
    public void readFileUpdateDatabaseCommit(String path) throws Exception {

        String sql = "update tb_user4 set name=?,age=?,address=?,card_no=?,open_date=?,toatl_amt=?,used_amt=?,extre_amt=?,create_time=current_timestamp,create_user='system',update_time=current_timestamp,update_user='jamysong' where id=?";
        ps = conn.prepareStatement(sql);
        try{
            File file = new File(path);
            BufferedReader br = new BufferedReader(new FileReader(file));
            String st;
            int i = 0;
            conn.setAutoCommit(false);
            while ((st = br.readLine()) != null ){
                i=i+1;
                String[] strs = st.split(",");
                ps.setString(1, strs[0]+"jamysong"+i);
                ps.setInt(2, Integer.parseInt(strs[1]));
                ps.setString(3, strs[2]);
                ps.setString(4, strs[3]);
                ps.setString(5, strs[4]);
                ps.setString(6, strs[5]);
                ps.setString(7, strs[6]);
                ps.setString(8, strs[7]);
                ps.setString(9, Integer.toString(i));
                ps.addBatch();
                if (i > 0 && i%10000==0) {
                    ps.executeBatch();
                    ps.clearBatch();
                    conn.commit();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
        }catch(Exception e){
            System.out.println("执行插入数据异常:" + e);
        }finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null){
                conn.close();
            }
        }
    }

    //多线程数据落库
    public void readFileUpdateDatabaseWithThread(String path) throws Exception {

        String sql = "update tb_user4 set name=?,age=?,address=?,card_no=?,open_date=?,toatl_amt=?,used_amt=?,extre_amt=?,create_time=current_timestamp,create_user='system',update_time=current_timestamp,update_user='jamysong' where id=?";
        ps0 = conn.prepareStatement(sql);
        ps1 = conn.prepareStatement(sql);
        ps2 = conn.prepareStatement(sql);
        ps3 = conn.prepareStatement(sql);
        ps4 = conn.prepareStatement(sql);

        System.out.println(ps0 == ps1);

        try{
            File file = new File(path);
            BufferedReader br = new BufferedReader(new FileReader(file));
            String st;
            int i = 0;
            int j = 0;
            int k = 0;
            int l = 0;
            int m = 0;
            int n = 0;
            conn.setAutoCommit(false);
            while ((st = br.readLine()) != null ){
                i=i+1;
                String[] strs = st.split(",");
                if (i%5 == 1) {
                    j = j+1;
                    ps0 = getPs(ps0, strs, i);
                    if (j%10000 == 0) {
                        executorService.submit(new JamyTask(ps0, conn));
                    }
                }else if(i%5 == 2) {
                    k = k + 1;
                    ps1 = getPs(ps1, strs, i);
                    if (k%10000 == 0) {
                        executorService.submit(new JamyTask(ps1, conn));
                    }
                }else if(i%5 == 3) {
                    l = l + 1;
                    ps2 = getPs(ps2, strs, i);
                    if (l%10000 == 0) {
                        executorService.submit(new JamyTask(ps2, conn));
                    }
                }else if(i%5 == 4) {
                    m = m + 1;
                    ps3 = getPs(ps3, strs, i);
                    if (m%10000 == 0) {
                        executorService.submit(new JamyTask(ps3, conn));
                    }
                }else if(i%5 == 0) {
                    n = n + 1;
                    ps4 = getPs(ps4, strs, i);
                    if (n % 10000 == 0) {
                        executorService.submit(new JamyTask(ps4, conn));
                    }
                }
            }
            if (j%10000 != 0) {
                executorService.submit(new JamyTask(ps0, conn));
            }
            if (k%10000 != 0) {
                executorService.submit(new JamyTask(ps1, conn));
            }
            if (l%10000 != 0){
                executorService.submit(new JamyTask(ps2, conn));
            }
            if (m%10000 != 0) {
                executorService.submit(new JamyTask(ps3, conn));
            }
            if (n%10000 != 0) {
                executorService.submit(new JamyTask(ps4, conn));
            }
        }catch(Exception e){
            System.out.println("执行插入数据异常:" + e);
        }finally {
            if (ps0 != null) {
                ps0.close();
            }
            if (ps1 != null) {
                ps1.close();
            }
            if (ps2 != null) {
                ps2.close();
            }
            if (ps3 != null) {
                ps3.close();
            }
            if (ps4 != null) {
                ps4.close();
            }
            if (conn != null){
                conn.close();
            }
        }
    }

    private PreparedStatement getPs(PreparedStatement ps, String[] strs, int i) throws SQLException {
        ps.setString(1, strs[0]+"ming"+i);
        ps.setInt(2, Integer.parseInt(strs[1]));
        ps.setString(3, strs[2]);
        ps.setString(4, strs[3]);
        ps.setString(5, strs[4]);
        ps.setString(6, strs[5]);
        ps.setString(7, strs[6]);
        ps.setString(8, strs[7]);
        ps.setString(9, Integer.toString(i));
        ps.addBatch();
        return ps;
    }
}
